package dao;

import dto.EstudioDTO;
import dto.HistoriaClinicaDTO;
import dto.TurnoDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import logic.Turno;

/**
 *
 * @author lucifer
 */
public class HistoriaClinicaDAO {

  public static final String Tab =
    "create table historia_clinica (" +
    "id serial," + //pk
    "id_paciente int" + //fk
    "); " +

    // Las patologías y alergias van fijas por paciente.
    "create table patologia (" +
    "id serial," + //pk
    "id_historia_clinica int," + //fk
    "nombre text" +
    "); " +

    "create table alergia (" +
    "id serial," + //pk
    "id_historia_clinica int," + //fk
    "nombre text" +
    "); " +

    // Los estudios van por turno y puede haber más de un estudio por turno.
          
    "create table estudio (" +
    "id serial," + //pk
    "id_turno int," + //fk
    "nombre text," +
    "detalle text" +
    "); " +      

    // En un turno el médico pudo haber escrito algo en el diagnóstico ó no.
    "create table diagnostico (" +
    "id serial," + //pk
    "id_turno int," + //fk
    "detalle text" +
    "); "

  ;

  public static final String Tuples =
    ""
  ;

  private static HistoriaClinicaDAO dao = null;
  private final Connection c;

  public static HistoriaClinicaDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new HistoriaClinicaDAO();
    }
    return dao;
  }

  private HistoriaClinicaDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("historia_clinica");
  }
  
  public int LastIDEstudio() throws SQLException {
    return DB.PQ().LastIDFrom("estudio");
  }

  public ArrayList<HistoriaClinicaDTO> SearchByPacienteID(int idPaciente) throws SQLException {
    return getHistoriaClinicaDTOs(queryByPacienteID(idPaciente));
  }

  private String queryByPacienteID(int idPaciente) throws SQLException {
    String query = String.format(
      "select * from historia_clinica where id_paciente = %d;", idPaciente
    );
    return query;
  }

  //FIXME
  private ArrayList<HistoriaClinicaDTO> getHistoriaClinicaDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<HistoriaClinicaDTO> dtos = new ArrayList<>();
    while (r.next()) {
      /*
      dtos.add(
        new HistoriaClinicaDTO(
          r.getInt("id"),
          r.getInt("id_paciente")
        )
      );
      */
    }
    r.close();
    s.close();
    return dtos;
  }

  //FIXME
  public HistoriaClinicaDTO HistoriaClinicaDTO(int id_paciente) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from historia_clinica where id_paciente = " + id_paciente
    );
    HistoriaClinicaDTO hdto = null;
    TurnoDAO tdao;
    ArrayList<TurnoDTO> tdtos;
    ArrayList<Integer> tids;
    if (r.next()) {
      tdao = TurnoDAO.createDAO();
      tdtos = tdao.SearchByPacienteID(id_paciente, Turno.asistido);
      tids = new ArrayList();
      for (TurnoDTO t: tdtos){
        tids.add(t.getId());
      }
      hdto = new HistoriaClinicaDTO(
        r.getInt("id"),
        r.getInt("id_paciente"),
        tids      
      );
    }
    r.close();
    s.close();
    return hdto; //si no existe devuelve null
  }

  //FIXME
  public void Save(HistoriaClinicaDTO h) throws SQLException {
    String query = String.format(
      "insert into historia_clinica values (default, %d );",
      h.getIdPaciente()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public void CrearHistoria(int id_paciente) throws SQLException{
    String query = String.format(
      "insert into historia_clinica values (default, %d );",
      id_paciente
      );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }

  public HistoriaClinicaDTO EditString(int id, String attr, String str) throws SQLException {
    String query = String.format(
      "update historia_clinica set %s = '%s' where id = %d;",
      attr, str, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return HistoriaClinicaDTO(id);
  }

  public HistoriaClinicaDTO EditInt(int id, String attr, int n) throws SQLException {
    String query = String.format(
      "update historia_clinica set %s = %d where id = %d;",
      attr, n, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return HistoriaClinicaDTO(id);
  }
  public void AddPatologia(int id_historia,String patologia) throws SQLException{
    String query = String.format(
      "insert into patologia values (default, '%d', '%s');",
      id_historia, patologia
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  public void AddAlergia(int id_historia,String alergia) throws SQLException{
    String query = String.format(
      "insert into alergia values (default, '%d', '%s');",
      id_historia, alergia
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  public void DelPatologia(int id_historia,String patologia) throws SQLException{
    String query = String.format(
      "delete from patologia where id_historia_clinica = '%d' and nombre = '%s'",
      id_historia, patologia
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  public void DelAlergia(int id_historia,String alergia) throws SQLException{
    String query = String.format(
      "delete from alergia where id_historia_clinica = '%d' and nombre = '%s'",
      id_historia, alergia
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  public ArrayList<String> getPatologias(int id_historia) throws SQLException{
    String query = String.format(
      "select nombre from patologia where id_historia_clinica = '%d' ",
      id_historia
    );
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<String> patologias = new ArrayList();
    while (r.next()) {
      patologias.add(
          r.getString("nombre")
      );
    }
    r.close();
    s.close();
    return patologias;
  }
  public ArrayList<String> getAlergias(int id_historia) throws SQLException{
    String query = String.format(
      "select nombre from alergia where id_historia_clinica = '%d' ",
      id_historia
    );
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<String> alergias = new ArrayList();
    while (r.next()) {
      alergias.add(
          r.getString("nombre")
      );
    }
    r.close();
    s.close();
    return alergias;
  }
  public void SaveEstudio(EstudioDTO edto) throws SQLException{
    String query = String.format(
      "insert into estudio values (default, '%d' , '%s' , '%s');",
      edto.getId_turno(),
      edto.getNombre(),
      edto.getDetalle()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public void EditEstudio(EstudioDTO e) throws SQLException{
    String query = String.format(
      "update estudio set nombre = '%s' , detalle = '%s' where id = '%d' ",
      e.getNombre(), e.getDetalle(), e.getId()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public void DelEstudio(int id) throws SQLException{
    String query = String.format(
      "delete from estudio where id = '%d' ",
      id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public ArrayList<EstudioDTO> getEstudios(int id_turno) throws SQLException{
    ArrayList<EstudioDTO> estudios = new ArrayList();
    String query = String.format(
      "select * from estudio where id_turno = '%d' ",
      id_turno
    );
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    while (r.next()) {
      estudios.add(new EstudioDTO(
          r.getInt("id"),
          r.getInt("id_turno"),
          r.getString("nombre"),
          r.getString("detalle")
      ));
    }
    r.close();
    s.close();
    return estudios;
  }
  
  public void AddDiagnostico(int id_turno,String detalle) throws SQLException{
    String query = String.format(
      "insert into diagnostico values (default, '%d', '%s');",
      id_turno, detalle
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public void DelDiagnostico(int id_turno) throws SQLException{
    String query = String.format(
      "delete from diagnostico where id_turno = '%d' ",
      id_turno
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public void EditDiagnostico(int id_turno,String detalle) throws SQLException{
    String query = String.format(
      "update diagnostico set detalle = '%s' where id_turno = '%d' ",
      detalle, id_turno
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }
  
  public String getDiagnostico(int id_turno) throws SQLException{
    String query = String.format(
      "select detalle from diagnostico where id_turno = '%d' ",
      id_turno
    );
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    String diagnostico = "";
    if (r.next()) {
          diagnostico = r.getString("detalle");
    }
    r.close();
    s.close();
    return diagnostico;
  }
}  
